package com.scuec.groupfive.mapper;

import com.github.pagehelper.Page;
import com.scuec.groupfive.entity.Post;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

@Mapper
public interface CommunityMapper {
    /**
     * 分页查询所有帖子（带用户信息）
     * @return 帖子分页列表
     */
    @Select("SELECT " +
            "p.post_id AS postId, " +
            "p.user_id AS userId, " +
            "p.title, " +
            "p.content, " +
            "p.view_count AS viewCount, " +
            "p.status, " +
            "p.created_at AS createdAt, " +
            "p.updated_at AS updatedAt, " +
            "p.tag, " +
            "p.type, " +
            "u.username, " +
            "u.avatar, " +
            "u.role AS userRole, " +
            "(SELECT COUNT(*) FROM post_comments c WHERE c.post_id = p.post_id) AS commentCount, " +
            "(SELECT COUNT(*) FROM post_likes l WHERE l.post_id = p.post_id) AS likeCount, " +
            "(SELECT COUNT(*) FROM post_favorites f WHERE f.post_id = p.post_id) AS favoriteCount " +
            "FROM posts p " +
            "JOIN users u ON p.user_id = u.user_id " +
            "ORDER BY p.created_at DESC")
    Page<Post> getAllPostsWithUserInfo();

    /**
     * 根据用户名查询帖子
     * @param username 用户名
     * @return 帖子分页列表
     */
    @Select("SELECT " +
            "p.post_id AS postId, " +
            "p.user_id AS userId, " +
            "p.title, " +
            "p.content, " +
            "p.view_count AS viewCount, " +
            "p.status, " +
            "p.created_at AS createdAt, " +
            "p.updated_at AS updatedAt, " +
            "p.tag, " +
            "p.type, " +
            "u.username, " +
            "u.avatar, " +
            "u.role AS userRole, " +
            "(SELECT COUNT(*) FROM post_comments c WHERE c.post_id = p.post_id) AS commentCount, " +
            "(SELECT COUNT(*) FROM post_likes l WHERE l.post_id = p.post_id) AS likeCount, " +
            "(SELECT COUNT(*) FROM post_favorites f WHERE f.post_id = p.post_id) AS favoriteCount " +
            "FROM posts p " +
            "JOIN users u ON p.user_id = u.user_id " +
            "WHERE u.username LIKE CONCAT('%', #{username}, '%') " +
            "ORDER BY p.created_at DESC")
    Page<Post> getPostsByUsername(@Param("username") String username);

    /**
     * 根据标题查询帖子
     * @param title 帖子标题
     * @return 帖子分页列表
     */
    @Select("SELECT " +
            "p.post_id AS postId, " +
            "p.user_id AS userId, " +
            "p.title, " +
            "p.content, " +
            "p.view_count AS viewCount, " +
            "p.status, " +
            "p.created_at AS createdAt, " +
            "p.updated_at AS updatedAt, " +
            "p.tag, " +
            "p.type, " +
            "u.username, " +
            "u.avatar, " +
            "u.role AS userRole, " +
            "(SELECT COUNT(*) FROM post_comments c WHERE c.post_id = p.post_id) AS commentCount, " +
            "(SELECT COUNT(*) FROM post_likes l WHERE l.post_id = p.post_id) AS likeCount, " +
            "(SELECT COUNT(*) FROM post_favorites f WHERE f.post_id = p.post_id) AS favoriteCount " +
            "FROM posts p " +
            "JOIN users u ON p.user_id = u.user_id " +
            "WHERE p.title LIKE CONCAT('%', #{title}, '%') " +
            "ORDER BY p.created_at DESC")
    Page<Post> getPostsByTitle(@Param("title") String title);
}